package com.zkn.component.office.excel.utils;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel Utility
 * @author frank.su 
 * @date 2017/08/01
 */
public class ExcelUtils {

	public static final List<String> FILE_TYPES = Arrays.asList(".xls", ".xlsx");
	
	private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss"; // TODO
	private static SimpleDateFormat format = new SimpleDateFormat(DATE_FORMAT);
    
    public static List<Map<String,Object>> readExcel2MapList4Sheet(Sheet sheet){
        return readData2MapList(sheet, readTitle(sheet));
    }
    
    public static List<List<String>> readData2StringList4Sheet(Sheet sheet){
    	return readData2StringList(sheet, readTitle(sheet));
    }
    
    public static List<List<String>> readExcel2StringList(InputStream is, boolean isFirstSheet) throws Exception{
    	List<List<String>> resultList= new ArrayList<List<String>>();
    	Workbook wb = null;
    	try {
	    	wb = WorkbookFactory.create(is);
	    	for(int a = 0; a < wb.getNumberOfSheets(); a++){
	    		resultList.addAll(readData2StringList4Sheet(wb.getSheetAt(a)));
	
	    		if(isFirstSheet){
	    			break;
	    		}
	    	}
    	} finally {
    		if(wb != null){
    			wb.close();
    		}
    		if(is != null){
    			is.close();
    		}
    	}
    	
    	return resultList;
    }
    
    public static List<Map<String,Object>> readExcel2MapList(InputStream is, boolean isFirstSheet) throws Exception{
        List<Map<String,Object>> mapList= new ArrayList<Map<String,Object>>();
        Workbook wb = null;
    	try {
    		wb = WorkbookFactory.create(is);
    		for(int a = 0; a < wb.getNumberOfSheets(); a++){
    			mapList.addAll(readExcel2MapList4Sheet(wb.getSheetAt(a)));
    			
    			if(isFirstSheet) {
    				break;
    			}
    		}
    	} finally {
    		if(wb != null){
    			wb.close();
    		}
    		if(is != null){
    			is.close();
    		}
    	}
       
        return mapList;
   }
    
    private static List<String> readTitle(Sheet sheet){
    	Row titleRow = sheet.getRow(0);
    	List<String> titleList = new ArrayList<String>();
        for (Cell cell : titleRow) {
      	  Object obj = getValue(cell);
      	  titleList.add(obj == null ? "" : obj.toString());
        }
        return titleList;
    }
    
    private static List<Map<String,Object>> readData2MapList(Sheet sheet, List<String> titleList){
    	List<Map<String,Object>> mapList= new ArrayList<Map<String,Object>>();
        for(int i = 1; i <= sheet.getLastRowNum(); i++){
            Map<String,Object> map = new HashMap<String, Object>();
            Row row = sheet.getRow(i);
            if(row != null){
         	   for(int j = 0; j < titleList.size(); j ++){
         		   map.put(titleList.get(j), getValue(row.getCell(j)));
         	   }
         	   if(isNotEmpty(map)){
         		  mapList.add(map);
               }
            }
       }
        return mapList;
    }
    
    private static List<List<String>> readData2StringList(Sheet sheet, List<String> titleList){
    	List<List<String>> resultList= new ArrayList<List<String>>();
		for(int i = 1; i <= sheet.getLastRowNum(); i++){
			List<String> dataList = new ArrayList<String>();
			Row row = sheet.getRow(i);
			if(row != null){
				for(int j = 0; j < titleList.size(); j ++){
					Object obj = getValue(row.getCell(j));
					dataList.add(object2String(obj));
				}
				if(isNotEmpty(dataList)){
					resultList.add(dataList);
				}
			}
		}
    	return resultList;
    }
    
    public static Object getValue(Cell cell) {
         if(cell == null){
        	 return null;
         }
         
         switch(cell.getCellTypeEnum()){
	         case NUMERIC: 
	        	 if(HSSFDateUtil.isCellDateFormatted(cell)){
	        		 return cell.getDateCellValue();
	        	 }
	        	 return cell.getNumericCellValue();
	         case BOOLEAN: 
	        	 return cell.getBooleanCellValue();
	         case STRING: 
	        	 return cell.getStringCellValue();
	         case FORMULA: 
	        	 if(!"".equals(cell.getStringCellValue())){
	        		 return cell.getStringCellValue();
	        	 } else {
	        		 return cell.getNumericCellValue();
	        	 }
	         case ERROR: 
	        	 return "";
	         default: return null;
         }
    }
    
    public static String object2String(Object obj){
    	if(obj == null){
    		return "";
    	} else if(obj instanceof Date){
    		return format.format(obj);
    	} else if(obj instanceof Number){
    		return String.valueOf(obj);
    	} else if(obj instanceof Boolean){
    		return String.valueOf(obj);
    	}
    	
    	return obj.toString();
    }
		   
	 public static Date parseDate(String dateString){
	 	try {
				return format.parse(dateString);
			} catch (ParseException e) {}
	 	
	 	return null;
	 }
    
	private static boolean isNotEmpty(Map<String,Object> dataMap){
		for(Entry<String, Object> ed : dataMap.entrySet()){
			if(ed.getValue() != null){
				return true;
			}
		}
		return false;
	}
	
	private static boolean isNotEmpty(List<String> list){
		for(String str : list){
			if(StringUtils.isNotBlank(str)){
				return true;
			}
		}
		return false;
	}
	
	public static void addLastColumn2Excel(String filepath, List<?> dataList) throws Exception{
		
		if(dataList == null || dataList.isEmpty()){
			return ;
		}
		
		OutputStream os = null;
		Workbook wb = null;
		try {
			wb = createWorkbook4Reader(filepath);
			
			Sheet sheet = wb.getSheetAt(0);
			int lastRowNumber = sheet.getLastRowNum();
			
			Row titleRow = sheet.getRow(0);
			short lastColumnNumber = titleRow.getLastCellNum();
			int addColumnNumber = lastColumnNumber;
			
			// Check inserting column existed
			if(existAddingColumn(titleRow.getCell(lastColumnNumber-1), dataList.get(0).toString())){
				return ;
			}
			
			Row row;
			for(int i = 0; i < dataList.size(); i ++){
				if(i > lastRowNumber) {
					row = sheet.createRow(i);
				} else {
					row = sheet.getRow(i);
				}
				Cell cell = row.createCell(addColumnNumber);
				setCellValue(cell, dataList.get(i));
			}
			   
			os = new FileOutputStream(filepath);
			wb.write(os);
			
		} finally {
			if(os != null){
				os.close();
			}
			if(wb != null){
				wb.close();
			}
		}
	}
	
	private static boolean existAddingColumn(Cell titleCell, String title){
		if(titleCell == null){
			return false;
		}
		
		if(titleCell.getStringCellValue().equals(title)) {
			return true;
		}
		
		return false;
	}
	
	public static void writeMap2Excel(String filepath, List<Map<String,Object>> dataList) throws Exception{
		
		if(dataList == null || dataList.isEmpty()){
			return ;
		}
		
		OutputStream os = null;
		Workbook wb = null;
		
		try {
			wb = createWorkbook4Writer(filepath);
			Sheet sheet = wb.createSheet();
			
			int rownum = 0;
			Row row = sheet.createRow(rownum);
			
			Cell cell;
			// Titlte
			int index = 0;
			List<String> titleList = new ArrayList<String>();
			for(String title : dataList.get(0).keySet()){
				cell = row.createCell(index++);
				cell.setCellValue(title);
				titleList.add(title);
			}
			
			for (Map<String,Object> map : dataList) {
				row = sheet.createRow(++rownum);
				index = 0;
				for(String title : titleList){
					cell = row.createCell(index++);
					setCellValue(cell, map.get(title));
				}
			}
			
			os = new FileOutputStream(filepath);
			wb.write(os);
		} finally {
			if(wb != null){
				wb.close();
			}
			if(os != null){
				os.close();
			}
		}
	}
	
    public static void setCellValue(Cell cell, Object obj) {
    	if(cell != null && obj != null){
    		if(obj instanceof Boolean){
    			cell.setCellValue((Boolean) obj);
    		} else if(obj instanceof Integer){
    			cell.setCellValue((Integer) obj);
    		} else if(obj instanceof Number){
    			cell.setCellValue((Double) obj);
    		} else if(obj instanceof Date){
    			cell.setCellValue(format.format(obj));
    		} else {
    			cell.setCellValue(obj.toString());
    		}
    	}
    }
    
    public static Workbook createWorkbook4Writer(String filepath) throws Exception{
    	Workbook wb = null;
		if(filepath.toLowerCase().endsWith(FILE_TYPES.get(0))){
			wb = new HSSFWorkbook();
		} else if(filepath.toLowerCase().endsWith(FILE_TYPES.get(1))){
			wb = new XSSFWorkbook();
		} else {
			throw new Exception("Please input an Excel file path.");
		}
		return wb;
    }
    
    public static Workbook createWorkbook4Reader(String filepath) throws Exception{
    	Workbook wb = null;
    	if(filepath.toLowerCase().endsWith(FILE_TYPES.get(0)) || filepath.toLowerCase().endsWith(FILE_TYPES.get(1))){
			wb = WorkbookFactory.create(new FileInputStream(filepath));
    	} else {
    		throw new Exception("Please input an Excel file path.");
    	}
    	return wb;
    }
}
